﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace SQLServerDataAccess
{
    public class DataAccess
    {
        private SqlConnection Conn;
        private SqlCommand Comm;
        private SqlDataAdapter Adapter;

        public DataAccess()
        {
            Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSqlConnection"].ToString());
            Comm = Conn.CreateCommand();
        }

        public DataAccess(string ConnectionStr)
        {
            Conn = new SqlConnection(ConnectionStr);
            Comm = Conn.CreateCommand();
        }

        public void SetDataSet(DataSet dataSet, CommandFill commandFill)
        {
            try
            {
                Conn.Open();
                Comm.CommandText = commandFill.CommandText;
                Comm.CommandType = commandFill.CommandType;
                Adapter = new SqlDataAdapter(Comm);
                Comm.Parameters.Clear();
                if (commandFill.Parameters != null)
                    foreach (string parameterName in commandFill.Parameters.Keys)
                        Comm.Parameters.Add(new SqlParameter(parameterName, commandFill.Parameters[parameterName] == null ? DBNull.Value : commandFill.Parameters[parameterName]));

                if (string.IsNullOrEmpty(commandFill.TableName))
                    Adapter.Fill(dataSet);
                else if (commandFill.MaxRecord < 1)
                    Adapter.Fill(dataSet, commandFill.TableName);
                else
                    Adapter.Fill(dataSet, commandFill.StartRecord, commandFill.MaxRecord, commandFill.TableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Conn.Close();
            }
        }

        public DataSet GetDataSet(CommandFill commandFill)
        {
            try
            {
                DataSet dataSet = new DataSet();
                SetDataSet(dataSet, commandFill);
                return dataSet;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetDataSet(List<CommandFill> commandFills)
        {
            try
            {
                DataSet dataSet = new DataSet();
                foreach (CommandFill commandFill in commandFills)
                    SetDataSet(dataSet, commandFill);
                return dataSet;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetTable(CommandFill commandFill)
        {
            try
            {
                DataSet dataSet = new DataSet();
                SetDataSet(dataSet, commandFill);
                return dataSet.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int TransactionExecute(CommandExecute commandExecute)
        {
            int counter = 0;
            Conn.Open();
            SqlTransaction sqlTransaction = Conn.BeginTransaction();
            try
            {
                Comm.Transaction = sqlTransaction;
                Comm.CommandText = commandExecute.CommandText;
                Comm.CommandType = commandExecute.CommandType;
                Comm.Parameters.Clear();
                if (commandExecute.Parameters != null)
                    foreach (string parameterName in commandExecute.Parameters.Keys)
                        Comm.Parameters.Add(new SqlParameter(parameterName, commandExecute.Parameters[parameterName] == null ? DBNull.Value : commandExecute.Parameters[parameterName]));
                counter += Comm.ExecuteNonQuery();
                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
                throw ex;
            }
            finally
            {
                Conn.Close();
                Comm.Transaction = null;
                sqlTransaction.Dispose();
            }
            return counter;
        }

        public int TransactionExecute(CommandMultiExecute commandMultiExecute)
        {
            int counter = 0;
            Conn.Open();
            SqlTransaction sqlTransaction = Conn.BeginTransaction();
            try
            {
                Comm.Transaction = sqlTransaction;
                Comm.CommandText = commandMultiExecute.CommandText;
                Comm.CommandType = commandMultiExecute.CommandType;
                foreach (Dictionary<string, object> parameter in commandMultiExecute.Parameters)
                {
                    Comm.Parameters.Clear();
                    if (parameter != null)
                        foreach (string parameterName in parameter.Keys)
                            Comm.Parameters.Add(new SqlParameter(parameterName, parameter[parameterName] == null ? DBNull.Value : parameter[parameterName]));
                    counter += Comm.ExecuteNonQuery();
                }
                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
                throw ex;
            }
            finally
            {
                Conn.Close();
                Comm.Transaction = null;
                sqlTransaction.Dispose();
            }
            return counter;
        }

        public int TransactionExecute(List<CommandMultiExecute> CommandMultiExecutes)
        {
            int counter = 0;
            Conn.Open();
            SqlTransaction sqlTransaction = Conn.BeginTransaction();
            try
            {
                Comm.Transaction = sqlTransaction;
                foreach (CommandMultiExecute CommandMultiExecute in CommandMultiExecutes)
                {
                    Comm.CommandText = CommandMultiExecute.CommandText;
                    Comm.CommandType = CommandMultiExecute.CommandType;
                    foreach (Dictionary<string, object> parameter in CommandMultiExecute.Parameters)
                    {
                        Comm.Parameters.Clear();
                        if (parameter != null)
                            foreach (string parameterName in parameter.Keys)
                                Comm.Parameters.Add(new SqlParameter(parameterName, parameter[parameterName] == null ? DBNull.Value : parameter[parameterName]));
                        counter += Comm.ExecuteNonQuery();
                    }
                }
                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
                throw ex;
            }
            finally
            {
                Conn.Close();
                Comm.Transaction = null;
                sqlTransaction.Dispose();
            }
            return counter;
        }

        public int TransactionExecute(List<CommandExecute> commandExecutes)
        {
            int counter = 0;
            Conn.Open();
            SqlTransaction sqlTransaction = Conn.BeginTransaction();
            try
            {
                Comm.Transaction = sqlTransaction;
                foreach (CommandExecute commandExecute in commandExecutes)
                {
                    Comm.CommandText = commandExecute.CommandText;
                    Comm.CommandType = commandExecute.CommandType;
                    Comm.Parameters.Clear();
                    if (commandExecute.Parameters != null)
                        foreach (string parameterName in commandExecute.Parameters.Keys)
                            Comm.Parameters.Add(new SqlParameter(parameterName, commandExecute.Parameters[parameterName] == null ? DBNull.Value : commandExecute.Parameters[parameterName]));
                    counter += Comm.ExecuteNonQuery();
                }
                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
                throw ex;
            }
            finally
            {
                Conn.Close();
                Comm.Transaction = null;
                sqlTransaction.Dispose();
            }
            return counter;
        }

        public object ExecuteScalar(CommandExecute commandExecute)
        {
            try
            {
                Conn.Open();
                Comm.CommandText = commandExecute.CommandText;
                Comm.CommandType = commandExecute.CommandType;
                Comm.Parameters.Clear();
                if (commandExecute.Parameters != null)
                    foreach (string parameterName in commandExecute.Parameters.Keys)
                        Comm.Parameters.Add(new SqlParameter(parameterName, commandExecute.Parameters[parameterName] == null ? DBNull.Value : commandExecute.Parameters[parameterName]));
                return Comm.ExecuteScalar();
            }
            catch (Exception ex) 
            {
                throw ex;
            }
            finally
            {
                Conn.Close();
            }
        }
    }
}
